﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using Model;

namespace Dal
{
    public class DalEmail
    {
        SqlCommand cmd = null;
        string sql = "";

        public DataTable Email_Inquire_All()
        {
            //获取全部信息
            SqlConnection conn = crecteSqlConnection.sqlconn();
            conn.Open();

            DataTable dt = null;
            SqlCommand cmd = null;
            try
            {
                sql = "select Id,Type,Title,Name,Handle from Mailbox where Deleted='False'";
                cmd = new SqlCommand(sql, conn);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                da.Fill(ds, "table");
                dt = ds.Tables["table"];
                if (dt != null)
                    return dt;
            }
            catch (Exception)
            {
                dt = null;
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
            return dt;
        }//获取全部信息

        public string Email_Director_Add(ModelPublic m)
        {

            SqlConnection conn = crecteSqlConnection.sqlconn();
            conn.Open();
            //主任信箱添加
            SqlCommand cmd = null;
            try
            {
                sql = "insert into Mailbox values(" + m.Name + "," + m.QQ + "," + m.Adress + "," + m.Phone + "," + m.Email + "," + m.Title + "," + m.Content + ",'主任信箱','未阅读','False','" + DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss") + "')";
                cmd = new SqlCommand(sql, conn);
                if (cmd.ExecuteNonQuery() > 0)
                {
                    return "留言成功！敬请等待处理结果！";
                }
            }
            catch (Exception)
            {
                return "留言错误！请联系管理员！";
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
            return "留言失败！\n请联系管理员！";
        }

        public string Email_Secretary_Add(ModelPublic m)
        {
            SqlConnection conn = crecteSqlConnection.sqlconn();
            conn.Open();
            //书记信箱添加
            SqlCommand cmd = null;
            try
            {
                sql = "insert into Mailbox values(" + m.Name + "," + m.QQ + "," + m.Adress + "," + m.Phone + "," + m.Email + "," + m.Title + "," + m.Content + ",'书记信箱','未阅读','False','" + DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss") + "')";
                cmd = new SqlCommand(sql, conn);
                if (cmd.ExecuteNonQuery() > 0)
                {
                    return "留言成功！敬请等待处理结果！";
                }
            }
            catch (Exception)
            {
                return "留言错误！请联系管理员！";
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
            return "留言失败！\n请联系管理员！";
        }

        public DataTable Email_Inquire_Single(ModelPublic m)
        {
            //查询单条信息
            SqlConnection conn = crecteSqlConnection.sqlconn();
            conn.Open();
            DataTable dt = null;
            try
            {
                sql = "select Id,Type,Title,Name,QQ,Phone,CONVERT(varchar(10), DateTime,120) as DateTime,Adress,EmailAdress,Handle,_Content from Mailbox where Id='" + m.Id + "' and Deleted='False'";
                cmd = new SqlCommand(sql, conn);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                da.Fill(ds, "table");
                dt = ds.Tables["table"];
                if (dt != null)
                    return dt;

            }
            catch (Exception)
            {
                dt = null;
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
            return dt;
        }//查询单条信息

        public bool Email_Read_Modify(ModelPublic m)
        {
            //是否查看
            SqlConnection conn = crecteSqlConnection.sqlconn();
            conn.Open();
            try
            {
                sql = "update Mailbox set EmailRead='true' where Id='" + m.Id + "'";
                cmd = new SqlCommand(sql, conn);
                if (cmd.ExecuteNonQuery() > 0)
                    return true;
            }
            catch (Exception)
            {
                return false;
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
            return false;
        }//是否查看

        public bool Email_Handle_Modify(ModelPublic m)
        {
            //浏览情况修改
            SqlConnection conn = crecteSqlConnection.sqlconn();
            conn.Open();
            try
            {
                sql = "update Mailbox set Handle='" + m.Handle + "' where Id='" + m.Id + "'";
                cmd = new SqlCommand(sql, conn);
                if (cmd.ExecuteNonQuery() > 0)
                    return true;
            }
            catch (Exception)
            {
                return false;
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
            return false;
        }//浏览情况修改

        public string Email_Handle_Search(ModelPublic m)
        {
            //浏览情况
            SqlConnection conn = crecteSqlConnection.sqlconn();
            conn.Open();
            SqlCommand cmd = null;
            try
            {
                sql = "Select Handle from Mailbox where Id='" + m.Id + "'";
                cmd = new SqlCommand(sql, conn);
                return cmd.ExecuteScalar().ToString();
            }
            catch (Exception)
            {

            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
            return "";
        }//浏览情况

        public bool Email_Delete(ModelPublic m)
        {
            //信息删除
            SqlConnection conn = crecteSqlConnection.sqlconn();
            conn.Open();
            try
            {
                sql = "delete from Mailbox where Id='" + m.Id + "'";
                cmd = new SqlCommand(sql, conn);
                if (cmd.ExecuteNonQuery() > 0)
                    return true;
            }
            catch (Exception)
            {

            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
            return false;
        }//信息删除

        public DataTable Email_Search_All(ModelPublic m)
        {
            //检索信息
            SqlConnection conn = crecteSqlConnection.sqlconn();
            conn.Open();

            DataTable dt = null;

            try
            {
                sql = "select Id,Type,Title,Name,Handle from Mailbox where (Handle LIKE '%"+m.Conditions+"%' or Type like '%" + m.Conditions + "%' or Title LIKE '%" + m.Conditions + "%' or DateTime like '%" + m.Conditions + "%' or Name like '%" + m.Conditions + "%') and Deleted='false' order by DateTime desc";

                cmd = new SqlCommand(sql, conn);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                da.Fill(ds);
                dt = ds.Tables[0];
                return dt;
            }
            catch (Exception)
            {
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
            return dt;
        }//检索信息
    }
}
